<html>
<!-- =====================================================================

  File:      Generated file for Adventure Works Cycles Storefront Sample
  Summary:   Self-documentation for application
  Date:	     June 16, 2003

=====================================================================

  This file is part of the Microsoft SQL Server Code Samples.
  Copyright (C) 2003 Microsoft Corporation.  All rights reserved.

This source code is intended only as a supplement to Microsoft
Development Tools and/or on-line documentation.  See these other
materials for detailed information regarding Microsoft code samples.

THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
PARTICULAR PURPOSE.

======================================================= -->
<head>
  <link rel=stylesheet href=style.css>
</head>
<body>
<div class=SourcePanel style="font-size:12">
<pre style="background-color:white">
<span style="color: #0000FF"><strong>using</strong></span> <span style="color: #2040a0">System</span><span style="color: #4444FF">;</span>
<span style="color: #0000FF"><strong>using</strong></span> <span style="color: #2040a0">System</span>.<span style="color: #2040a0">Data</span><span style="color: #4444FF">;</span>
<span style="color: #0000FF"><strong>using</strong></span> <span style="color: #2040a0">System</span>.<span style="color: #2040a0">Data</span>.<span style="color: #2040a0">SqlClient</span><span style="color: #4444FF">;</span>
<span style="color: #0000FF"><strong>using</strong></span> <span style="color: #2040a0">System</span>.<span style="color: #2040a0">Configuration</span><span style="color: #4444FF">;</span>
<span style="color: #0000FF"><strong>using</strong></span> <span style="color: #2040a0">System</span>.<span style="color: #2040a0">Globalization</span><span style="color: #4444FF">;</span>

<span style="color: #008000">/* =====================================================================

  File:      ReviewWatch.cs for Adventure Works Cycles ReviewWatcher Sample
  Summary:   Defines the ReviewWatch class which sets up a query notification to monitor
			 new rows added to the ProductReview table, and inserts the data contained
			 in those new rows into the user interface in ReviewWatchForm.cs.
  Date:	     September 17, 2003

---------------------------------------------------------------------

  This file is part of the Microsoft SQL Server Code Samples.
  Copyright (C) 2003 Microsoft Corporation.  All rights reserved.

This source code is intended only as a supplement to Microsoft
Development Tools and/or on-line documentation.  See these other
materials for detailed information regarding Microsoft code samples.

THIS CODE AND INFORMATION ARE PROVIDED &quot;AS IS&quot; WITHOUT WARRANTY OF ANY
KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
PARTICULAR PURPOSE.

======================================================= */</span>

<span style="color: #0000FF"><strong>namespace</strong></span> <span style="color: #2040a0">Microsoft</span>.<span style="color: #2040a0">Samples</span>.<span style="color: #2040a0">SqlServer</span>
<span style="color: #4444FF"><strong>{</strong></span>
	<span style="color: #008000">/// &lt;summary&gt;</span>
	<span style="color: #008000">/// This class sets up a query notification to monitor</span>
	<span style="color: #008000">///	new rows added to the ProductReview table, and inserts the data contained</span>
	<span style="color: #008000">///	in those new rows into the user interface in ReviewWatchForm.cs.</span>
	<span style="color: #008000">/// &lt;/summary&gt;</span>
	<span style="color: #0000FF"><strong>public</strong></span> <span style="color: #0000FF"><strong>class</strong></span> <span style="color: #2040a0">ReviewWatch</span>
	<span style="color: #4444FF"><strong>{</strong></span>
		<span style="color: #0000FF"><strong>private</strong></span> <span style="color: #2040a0">Int32</span> <span style="color: #2040a0">CurrentReviewID</span> <span style="color: #4444FF">=</span> <span style="color: #FF0000">0</span><span style="color: #4444FF">;</span>  <span style="color: #008000">//The primary key of the last product review row we've processed so far.</span>
		<span style="color: #0000FF"><strong>private</strong></span> <span style="color: #2040a0">ReviewWatchForm</span> <span style="color: #2040a0">form</span><span style="color: #4444FF">;</span>       <span style="color: #008000">//The part of the user interface we should update when new rows arrive.</span>
		<span style="color: #0000FF"><strong>private</strong></span> <span style="color: #2040a0">SqlDependency</span> <span style="color: #2040a0">dependency</span><span style="color: #4444FF">;</span>	<span style="color: #008000">//The request to receive notifications when a query result changes.  We need </span>
		<span style="color: #008000">//to hang on to this object so that it is not garbage collected.</span>
		<span style="color: #008000">/// &lt;summary&gt;</span>
		<span style="color: #008000">///		Initializes an instance of the class.  Keeps track of the ui object to update when new rows are </span>
		<span style="color: #008000">///		discovered, and determines what the last product review key was.</span>
		<span style="color: #008000">/// &lt;/summary&gt;</span>
		<span style="color: #008000">/// &lt;param name=&quot;form&quot;&gt;The ui object to update when new product review rows are inserted&lt;/param&gt;</span>
		<span style="color: #0000FF"><strong>public</strong></span> <span style="color: #2040a0">ReviewWatch</span><span style="color: #4444FF">(</span><span style="color: #2040a0">ReviewWatchForm</span> <span style="color: #2040a0">form</span><span style="color: #4444FF">)</span>
		<span style="color: #4444FF"><strong>{</strong></span>
			<span style="color: #008000">//Get the ID of the last review</span>
			<span style="color: #0000FF"><strong>using</strong></span> <span style="color: #4444FF">(</span><span style="color: #2040a0">SqlConnection</span> <span style="color: #2040a0">conn</span> <span style="color: #4444FF">=</span>
								 <span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">SqlConnection</span><span style="color: #4444FF">(</span><span style="color: #2040a0">Properties</span>.<span style="color: #2040a0">Settings</span>.<span style="color: #2040a0">Default</span>.<span style="color: #2040a0">ConnectionString</span><span style="color: #4444FF">)</span><span style="color: #4444FF">)</span>
			<span style="color: #4444FF"><strong>{</strong></span>
				<span style="color: #2040a0">SqlCommand</span> <span style="color: #2040a0">cmd</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">conn</span>.<span style="color: #2040a0">CreateCommand</span><span style="color: #4444FF">(</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>

				<span style="color: #2040a0">cmd</span>.<span style="color: #2040a0">CommandText</span> <span style="color: #4444FF">=</span>
				<span style="color: #444444">&quot;SELECT TOP 1 ProductReviewID FROM Production.ProductReview ORDER BY ProductReviewID DESC&quot;</span><span style="color: #4444FF">;</span>
				<span style="color: #2040a0">conn</span>.<span style="color: #2040a0">Open</span><span style="color: #4444FF">(</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>

				<span style="color: #2040a0">object</span> <span style="color: #2040a0">result</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">cmd</span>.<span style="color: #2040a0">ExecuteScalar</span><span style="color: #4444FF">(</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>

				<span style="color: #008000">// If there are reviews, set CurrentReviewID to the ReviewID of the latest one. If not, do nothing.</span>
				<span style="color: #0000FF"><strong>if</strong></span> <span style="color: #4444FF">(</span><span style="color: #2040a0">result</span> <span style="color: #2040a0">is</span> <span style="color: #2040a0">Int32</span><span style="color: #4444FF">)</span> <span style="color: #2040a0">CurrentReviewID</span> <span style="color: #4444FF">=</span> <span style="color: #4444FF">(</span><span style="color: #2040a0">Int32</span><span style="color: #4444FF">)</span><span style="color: #2040a0">result</span><span style="color: #4444FF">;</span>
			<span style="color: #4444FF"><strong>}</strong></span>

			<span style="color: #008000">//Keep track of the ui object to update.</span>
			<span style="color: #0000FF"><strong>this</strong></span>.<span style="color: #2040a0">form</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">form</span><span style="color: #4444FF">;</span>
		<span style="color: #4444FF"><strong>}</strong></span>
		<span style="color: #008000">/// &lt;summary&gt;</span>
		<span style="color: #008000">///		This method is used to create the SqlDependency class which will be used to obtain notifications when</span>
		<span style="color: #008000">///		a row is added to the ProductReview table.  It also reads any new rows and updates the user interface </span>
		<span style="color: #008000">///		accordingly.  This is called one time when the user interface is initialized, and also once every time a notification</span>
		<span style="color: #008000">///		is received.</span>
		<span style="color: #008000">/// &lt;/summary&gt;</span>
		<span style="color: #0000FF"><strong>public</strong></span> <span style="color: #0000FF"><strong>void</strong></span> <span style="color: #2040a0">WatchForReviews</span><span style="color: #4444FF">(</span><span style="color: #4444FF">)</span>
		<span style="color: #4444FF"><strong>{</strong></span>
			<span style="color: #0000FF"><strong>using</strong></span> <span style="color: #4444FF">(</span><span style="color: #2040a0">SqlConnection</span> <span style="color: #2040a0">conn</span> <span style="color: #4444FF">=</span>
                                 <span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">SqlConnection</span><span style="color: #4444FF">(</span><span style="color: #2040a0">Properties</span>.<span style="color: #2040a0">Settings</span>.<span style="color: #2040a0">Default</span>.<span style="color: #2040a0">ConnectionString</span><span style="color: #4444FF">)</span><span style="color: #4444FF">)</span>
			<span style="color: #4444FF"><strong>{</strong></span>
				<span style="color: #008000">// Construct the command to get any new ProductReview rows from the database along with the corresponding product name</span>
				<span style="color: #008000">// from the Product table.</span>
				<span style="color: #2040a0">SqlCommand</span> <span style="color: #2040a0">cmd</span> <span style="color: #4444FF">=</span> <span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">SqlCommand</span><span style="color: #4444FF">(</span>
				<span style="color: #444444">&quot;SELECT PR.ProductReviewID, P.[Name], PR.Rating, PR.ReviewerName, PR.ReviewDate, PR.Comments&quot;</span> <span style="color: #4444FF">+</span>
					<span style="color: #444444">&quot; FROM Production.ProductReview as PR JOIN Production.Product as P ON P.ProductID = PR.ProductID&quot;</span> <span style="color: #4444FF">+</span>
					<span style="color: #444444">&quot; WHERE ProductReviewID &gt; @CurrentReviewID&quot;</span> <span style="color: #4444FF">+</span>
					<span style="color: #444444">&quot; ORDER BY ProductReviewID ASC&quot;</span>, <span style="color: #2040a0">conn</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>

				<span style="color: #2040a0">cmd</span>.<span style="color: #2040a0">Parameters</span>.<span style="color: #2040a0">Add</span><span style="color: #4444FF">(</span><span style="color: #444444">&quot;@CurrentReviewID&quot;</span>, <span style="color: #2040a0">SqlDbType</span>.<span style="color: #2040a0">Int</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
				<span style="color: #2040a0">cmd</span>.<span style="color: #2040a0">Parameters</span><span style="color: #4444FF">[</span><span style="color: #FF0000">0</span><span style="color: #4444FF">]</span>.<span style="color: #2040a0">Value</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">CurrentReviewID</span><span style="color: #4444FF">;</span>

				<span style="color: #008000">// Create a dependency on this query.</span>
				<span style="color: #2040a0">dependency</span> <span style="color: #4444FF">=</span> <span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">SqlDependency</span><span style="color: #4444FF">(</span><span style="color: #2040a0">cmd</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>

				<span style="color: #008000">// Register the event handler which will be called when new rows are added to the ProductReview table.</span>
				<span style="color: #2040a0">dependency</span>.<span style="color: #2040a0">OnChanged</span> <span style="color: #4444FF">+</span><span style="color: #4444FF">=</span>
				<span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">OnChangedEventHandler</span><span style="color: #4444FF">(</span><span style="color: #2040a0">Dependency_OnChanged</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
				<span style="color: #2040a0">conn</span>.<span style="color: #2040a0">Open</span><span style="color: #4444FF">(</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>

				<span style="color: #008000">// Get any new rows</span>
				<span style="color: #2040a0">SqlDataReader</span> <span style="color: #2040a0">rdr</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">cmd</span>.<span style="color: #2040a0">ExecuteReader</span><span style="color: #4444FF">(</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>

				<span style="color: #008000">// Process the rows by updating the user interface with the data returned for each row.</span>
				<span style="color: #0000FF"><strong>while</strong></span> <span style="color: #4444FF">(</span><span style="color: #2040a0">rdr</span>.<span style="color: #2040a0">Read</span><span style="color: #4444FF">(</span><span style="color: #4444FF">)</span><span style="color: #4444FF">)</span>
				<span style="color: #4444FF"><strong>{</strong></span>
					<span style="color: #2040a0">Int32</span> <span style="color: #2040a0">reviewID</span> <span style="color: #4444FF">=</span> <span style="color: #4444FF">(</span><span style="color: #2040a0">Int32</span><span style="color: #4444FF">)</span><span style="color: #2040a0">rdr</span>.<span style="color: #2040a0">GetInt32</span><span style="color: #4444FF">(</span><span style="color: #FF0000">0</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>

					<span style="color: #008000">// This &quot;if&quot; statement is necessary as it is possible to get a notification even when there is no new data.</span>
					<span style="color: #0000FF"><strong>if</strong></span> <span style="color: #4444FF">(</span><span style="color: #2040a0">reviewID</span> <span style="color: #4444FF">&gt;</span> <span style="color: #2040a0">CurrentReviewID</span><span style="color: #4444FF">)</span>
					<span style="color: #4444FF"><strong>{</strong></span>
						<span style="color: #008000">//Note that just calling the AddRowToList method will not work correctly as the event is </span>
						<span style="color: #008000">//processed in a different thread from the control's thread.  So, we must use Invoke to make</span>
						<span style="color: #008000">//sure the form is updated in the control's thread.</span>
						<span style="color: #2040a0">form</span>.<span style="color: #2040a0">Invoke</span><span style="color: #4444FF">(</span><span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">ReviewWatchForm</span>.<span style="color: #2040a0">AddReviewerRow</span><span style="color: #4444FF">(</span><span style="color: #2040a0">form</span>.
																	   <span style="color: #2040a0">AddRowToList</span><span style="color: #4444FF">)</span>, <span style="color: #0000FF"><strong>new</strong></span>
							<span style="color: #2040a0">object</span><span style="color: #4444FF">[</span><span style="color: #4444FF">]</span> <span style="color: #4444FF"><strong>{</strong></span>
							<span style="color: #2040a0">rdr</span>.<span style="color: #2040a0">GetInt32</span><span style="color: #4444FF">(</span><span style="color: #FF0000">2</span><span style="color: #4444FF">)</span>, <span style="color: #2040a0">rdr</span>.<span style="color: #2040a0">GetString</span><span style="color: #4444FF">(</span><span style="color: #FF0000">1</span><span style="color: #4444FF">)</span>,
							<span style="color: #2040a0">rdr</span>.<span style="color: #2040a0">GetString</span><span style="color: #4444FF">(</span><span style="color: #FF0000">3</span><span style="color: #4444FF">)</span>,
							<span style="color: #2040a0">rdr</span>.<span style="color: #2040a0">GetDateTime</span><span style="color: #4444FF">(</span><span style="color: #FF0000">4</span><span style="color: #4444FF">)</span>.<span style="color: #2040a0">ToString</span><span style="color: #4444FF">(</span><span style="color: #2040a0">CultureInfo</span>.
														<span style="color: #2040a0">CurrentUICulture</span><span style="color: #4444FF">)</span>, <span style="color: #2040a0">rdr</span>.
																		   <span style="color: #2040a0">GetString</span><span style="color: #4444FF">(</span><span style="color: #FF0000">5</span><span style="color: #4444FF">)</span>
						<span style="color: #4444FF"><strong>}</strong></span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
						<span style="color: #2040a0">CurrentReviewID</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">reviewID</span><span style="color: #4444FF">;</span>
					<span style="color: #4444FF"><strong>}</strong></span>
				<span style="color: #4444FF"><strong>}</strong></span>
				<span style="color: #008000">//Note: We do not need to close the reader as the &quot;using&quot; statement will dispose of the connection.</span>
			<span style="color: #4444FF"><strong>}</strong></span>
		<span style="color: #4444FF"><strong>}</strong></span>
		<span style="color: #008000">/// &lt;summary&gt;</span>
		<span style="color: #008000">///		This is the event handler which is invoked when new rows have been added to the ProductReview table.</span>
		<span style="color: #008000">///		It simply calls WatchForReviews method.  It is important to call that method rather than just perform the </span>
		<span style="color: #008000">///		data access because the previous dependency is cancelled when the notification is fired (&quot;fire and forget&quot;), so </span>
		<span style="color: #008000">///     reregistration of the dependency is essential.</span>
		<span style="color: #008000">/// &lt;/summary&gt;</span>
		<span style="color: #008000">/// &lt;param name=&quot;sender&quot;&gt;Who sent the event&lt;/param&gt;</span>
		<span style="color: #008000">/// &lt;param name=&quot;e&quot;&gt;Additional information about the event&lt;/param&gt;</span>
		<span style="color: #0000FF"><strong>private</strong></span> <span style="color: #0000FF"><strong>void</strong></span> <span style="color: #2040a0">Dependency_OnChanged</span><span style="color: #4444FF">(</span><span style="color: #2040a0">object</span> <span style="color: #2040a0">sender</span>,
										  <span style="color: #2040a0">SqlNotificationEventArgs</span> <span style="color: #2040a0">e</span><span style="color: #4444FF">)</span>
		<span style="color: #4444FF"><strong>{</strong></span>
			<span style="color: #2040a0">WatchForReviews</span><span style="color: #4444FF">(</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
		<span style="color: #4444FF"><strong>}</strong></span>
	<span style="color: #4444FF"><strong>}</strong></span>
<span style="color: #4444FF"><strong>}</strong></span>
</pre>
